import pymysql
from datetime import datetime
import uuid
import json


def __getDBConnect():
    '''
    获取DB连接
    '''
    host = "localhost"
    username = "root"
    password = "root"
    dbname = "spider"
    db = pymysql.connect(host, username, password, dbname)
    return db


def getResource():
    '''
    获取当前可用的公众号资源
    '''
    db = __getDBConnect()
    query = "select * from resource where status = 0 limit 1"
    cursor = db.cursor()
    cursor.execute(query);
    data = cursor.fetchone()
    db.close()
#     id = data[0]
#     sub_name = data[1]
#     token = data[2]
#     cookie = data[3]
#     print("id=%s,sub_name=%s,token=%s,cookie=%s" % (id, sub_name, token, cookie ))
    return data



def update_resource(id, status):
    '''
    更新当前公众号资源
    0 -> 正常
    1 -> 使用中
    2 -> 异常
    '''
    db = __getDBConnect()
    sql = "update resource set status = %d where id = '%s'" % (status, id)
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()
    db.close()



def getTask():
    '''
    获取当前正在等待爬取的公众号信息
    return: ('48EBE71639AD02F8F81E80C5BD70CA7B', 'laoyaodainichixian', None, datetime.datetime(2018, 12, 24, 19, 3, 55), None, 2, None)
    '''
    db = __getDBConnect()
    sql = "select * from subscription where sub_status > 0 and sub_status < 3 order by sub_input_time ASC limit 1"
    cursor = db.cursor()
    cursor.execute(sql);
    data = cursor.fetchone()
    db.close()
#     print(data)
#     id = data[0]
#     sub_id = data[1]
#     sub_name = data[2]
#     print("id=%s,sub_id=%s,sub_name=%s" % (id,sub_id,sub_name))
    return data

def getTask_page():
    db = __getDBConnect()
    sql = "select * from page where p_status = 2 limit 1"
    cursor = db.cursor()
    cursor.execute(sql);
    data = cursor.fetchone()
    db.close()
    return data
    
def updateSubscription(data):
    '''
    更新公众号信息
    '''
    db = __getDBConnect()
    sql = "update subscription set sub_id = '%s', sub_name = '%s', sub_status = %d, sub_json = '%s', spider_step = '%s', fakeid = '%s' where id = '%s'" % (data[1], data[2], data[5], data[6], data[7], data[8], data[0])
#     print(sql)
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()
    db.close()



def updateSubscriptionStatus(data):
    '''
    更新公众号状态为完成(0)
    '''
    db = __getDBConnect()
    sql = "update subscription set sub_status = %d,sub_collect_time = str_to_date('%s','%%Y-%%m-%%d %%H:%%i:%%s') where id = '%s'" % (data[5], datetime.now().strftime("%Y-%m-%d %H:%M:%S"), data[0])
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()
    db.close()



def insertPageInfo(data,sub):
    '''
    插入待爬取的页面信息
    '''
    str_page = str(data[7]).replace("\"", '#')
    str_page = str_page.replace("'", '"')
    try:
        dic_page = json.loads(str_page)
    except Exception:
        return
    publicTime = datetime.fromtimestamp(dic_page["update_time"])
    title = dic_page["title"]
    p_link = dic_page["link"]
    db = __getDBConnect()
    uuid_str  = str(uuid.uuid1()).replace("-", "")
    sql = "insert into page(id,p_title,p_public_time,p_status,p_link,p_data) values ('%s','%s',str_to_date('%s','%%Y-%%m-%%d %%H:%%i:%%s'),%d,'%s','%s')" % (uuid_str, title, publicTime, data[4], p_link, str_page)
    sql2 = "insert into sub_page(sub_id,page_id) values ('%s','%s')" % (sub[0], uuid_str)

    cursor = db.cursor()
#     try:
#         cursor.execute(sql)
#         cursor.execute(sql2)
#         db.commit()
#     except:
#         db.rollback()
    cursor.execute(sql)
    cursor.execute(sql2)
    db.commit()
    db.close()



def updatePageInfo(page):
    '''
    更新页面信息，插入爬取结果/公众号和页面关联
    '''
    db = __getDBConnect()
    
    sql1 = "update page set p_collect_time = str_to_date('%s','%%Y-%%m-%%d %%H:%%i:%%s'), p_status = %d, p_file= '%s' where id = '%s'" % (datetime.now().strftime("%Y-%m-%d %H:%M:%S"), page[4], page[6], page[0])
#     print(sql1)
    cursor = db.cursor()
    try:
        cursor.execute(sql1)
        db.commit()
    except:
        db.rollback()
    db.close()

    
if __name__ == "__main__":
#     data = getResource()
#     print(data)
#     update_resource('BCE0ACE476311B88EEC934F3FBF2CB77',1)
#     getTask()
#     data = ("DB0D126DA90BEB6F132BFFB2944E98AB","laoyaodainichixian","123",None,None,0)
#     updateSubscription(data)
#     updateSubscriptionStatus(data)
#     j_page = "{'aid': '2657310383_1', 'appmsgid': 2657310383, 'cover': 'https://mmbiz.qlogo.cn/mmbiz_jpg/cyliaI8R42476nrB2QPaicoBTRA12WSkUJkGuEEJjjX0j0cicCUDqgjQJF5hD05zh6gU1HkVmSCLCicOZPyYnicUw1Q/0?wx_fmt=jpeg', 'digest': '', 'item_show_type': 0, 'itemidx': 1, 'link': 'http://mp.weixin.qq.com/s?__biz=MjM5NDcxNzkyMQ==&mid=2657310383&idx=1&sn=1f22eb6b8a35ff213f44897fa6900df2&chksm=bd147f218a63f6375aa46bafb5215a56b3e9ced266fd813d22dbdb62fbe57f7f5c095ca1c6a1#rd', 'title': '郑州茶饮界的扛把子 性价比超高的高颜值茶饮空降西安，你尝鲜了么？', 'update_time': 1544969039}";
#     data = ("321f8aec076311e98a43c454445868c0", "", "", "", 0, "123", "123", j_page)
#     insertPageInfo(data)
#     sub = ("DB0D126DA90BEB6F132BFFB2944E98AB","123")
#     updatePageInfo(data, sub)
    print(1)
